const { needParam } = require("../../scripts/note/core/compile/mysql/_utils");
const { getOmitFieldNames, getUpdateParams, getWhereStr } = require("../../scripts/note/core/creator/mysql/_utils");
const { N } = require("../../scripts/note/core/utils");
const { consoleLog } = require("../../utils");
const { defaultOrder, defaultLimits } = require("../../utils/const");
const { selectTable, deleteTable, updateTable, insertTable, insertOrUpdateTable, getTable } = require("../../utils/sql");
const { getSomeFieldNames, getStandFieldNames } = require("../../scripts/note/core/compile/mysql/_utils");

const Service = require("egg").Service;

// 打印sal语句日志
function printSqlLog(sql, type = "list") {
  const titleMap = {
    insert: "新增",
    delete: "删除",
    update: "更新",
    detail: "查详情",
    find: "查记录",
    countTotal: "查总数",
    list: "查列表",
  };
  const title = titleMap[type];
  consoleLog(`----- ${title} Sql -----${N}${sql}${N}`, "success");
}

/**
 * 判断是否是sql语句
 */
function getInferIsSqlStr(ele) {
  if (typeof ele !== "string") return false;
  if (!ele) return false;
  ele = ele.trim();
  if (ele.charAt(0) === "(") return true; // 如果以小括号开头，则是sql语句
  const firstInd = ele.indexOf(" ");
  const word = ele.slice(0, firstInd).toUpperCase();
  // if (word.length !== 6) return false;
  const keywords = ["INSERT", "DELETE", "UPDATE", "SELECT"];
  // return keywords.includes(word);
  return keywords.find(keyword => word.startsWith(keyword));
}

// 可选值： insert, update, list, detail, find, countTotal
const logType = "list";
module.exports = class CommonService extends Service {
  /**
   * 新增
   * @param {string} tableName // 表格名称
   * @param {array} rows 要插入的数据（二维数组）
   * @returns
   */
  // * @param {object} existWhere 插入前要判断该记录是否存在
  // rows = [
  //     ["question", "content"],
  //     ["问题1", "内容1"],
  //     ["问题2", "内容2"],
  // ]
  async insert(tableName = needParam(), rows = needParam(), uniqueKey = "id") {
    const isSqlStr = getInferIsSqlStr(tableName);
    let sql = tableName;
    if (!isSqlStr) {
      const where = { [uniqueKey]: rows[0]?.[uniqueKey] };
      const hasWhere = Object.values(where).find(it => it !== undefined);
      if (hasWhere) {
        const target = await this.service.common.detail(tableName, { where });
        if (target) return 1;
      }
      sql = insertTable(tableName, rows);
    }
    logType === "insert" && printSqlLog(sql, "insert");
    const result = await this.app.mysql.query(sql);
    if (result.affectedRows > 0) return 0;
  }
  // 删除
  async delete(tableName = needParam(), params = needParam()) {
    const isSqlStr = getInferIsSqlStr(tableName);
    let sql = tableName;
    if (!isSqlStr) {
      sql = deleteTable(tableName, { where: params });
    }
    logType === "update" && printSqlLog(sql, "delete");
    const result = await this.app.mysql.query(sql);
    if (result.affectedRows > 0) return 0;
    return 99;
  }
  // 更改
  async update(tableName = needParam(), fields = {}, params = needParam()) {
    const isSqlStr = getInferIsSqlStr(tableName);
    let sql = tableName;
    if (!isSqlStr) {
      const { where } = params;
      const userId = this.service.user.getUserId();
      const updateParams = getUpdateParams(tableName, fields, userId);
      sql = updateTable(tableName, updateParams, { where });
    }
    true && printSqlLog(sql, "update");
    const result = await this.app.mysql.query(sql);
    if (result.affectedRows > 0) return 0;
    return 99;
  }

  // 插入或更新
  async insertOrUpdate(tableName = needParam(), insertKeyRows = [], fields = { status: "NOT status" }) {
    const isSqlStr = getInferIsSqlStr(tableName);
    let sql = tableName;
    if (!isSqlStr) {
      const userId = this.service.user.getUserId();
      const updateParams = getUpdateParams(tableName, fields, userId);
      sql = insertOrUpdateTable(tableName, insertKeyRows, updateParams);
    }
    true && printSqlLog(sql, "insertOrUpdate");
    const result = await this.app.mysql.query(sql);
    if (result.affectedRows > 0) return 0;
    return 99;
  }

  // 查列表
  async list(tables = needParam(), { whereParams, order = defaultOrder, limits = defaultLimits }, parseKeys) {
    const isSqlStr = getInferIsSqlStr(tables);
    let sql = tables;
    if (!isSqlStr) {
      sql = selectTable(tables, { where: whereParams, order, limits });
    }
    logType === "list" && printSqlLog(sql, "list");
    const data = await this.app.mysql.query(sql);
    // 处理JSON字符串
    if (parseKeys?.length) {
      data.forEach(item => {
        parseKeys.forEach(key => {
          item[key] = JSON.parse(item[key]);
        });
      });
    }
    return data;
  }
  // 查详情（只查一条，返回对象）
  async detail(tables = needParam(), queryFilters, keyVals) {
    const isSqlStr = getInferIsSqlStr(tables);
    let sql = tables;
    if (!isSqlStr) {
      sql = selectTable(tables, queryFilters);
    }
    logType === "detail" && printSqlLog(sql, "detail");
    const data = await this.app.mysql.query(sql);
    if (!keyVals?.length) {
      if (data.length > 1) throw new Error(`查出了${data.length}条数据：${sql}`);
      return data[0];
    } else {
      const obj = {};
      if (!keyVals?.length) throw new Error(`请传入键值对`);
      const [key, val] = keyVals;
      data.forEach(row => (obj[row[key]] = row[val]));
      return obj;
    }
  }
  // 查记录（可能多条，返回数组）
  async find(tables = needParam(), params) {
    let sql = tables;
    if (!getInferIsSqlStr(tables)) {
      sql = selectTable(tables, { where: params, order: defaultOrder, limits: defaultLimits });
    }
    logType === "find" && printSqlLog(sql, "find");
    return await this.app.mysql.query(sql);
  }
  // 查总数
  // sqlOpts 例：{ where, order, limits, group, having }
  async countTotal(tableName = needParam(), sqlOpts = needParam()) {
    let sql = tableName;
    if (!getInferIsSqlStr(tableName)) {
      sql = getTable(tableName, "count", sqlOpts);
    }
    logType === "countTotal" && printSqlLog(sql, "countTotal");
    const result = await this.app.mysql.query(sql);
    return result[0].count;
  }

  // 总数+-1
  async countUpdate(tableName = needParam(), fields = [], sqlOpts = needParam(), step = 1) {
    // const sql = `UPDATE note SET view_num = view_num + 1  WHERE id = 1;`;
    // const sql = `UPDATE note SET view_num = LAST_INSERT_ID(view_num + 1) WHERE id = 1;`;
    // const sql = `UPDATE note SET view_num = view_num - 1  WHERE id = 1;`;
    // const sql = `UPDATE note SET view_num = GREATEST(view_num - 1, 0) WHERE id = 1;`; // 保证不会减到负数
    const { where } = sqlOpts;
    let sql = `UPDATE ${tableName}`;
    const fieldsStr = fields
      .map(field => {
        if (step >= 0) return `${field} = LAST_INSERT_ID(${field} + ${step}) `;
        // return `${field} = GREATEST(${field} - ${Math.abs(step)}, 0)`;
        return `${field} = IF(${field} > 0, ${field} - ${Math.abs(step)}, 0)`;
      })
      .join(", ");
    if (fieldsStr) sql += ` SET ${fieldsStr}`;
    const whereStr = getWhereStr(where, getStandFieldNames(["*"], tableName));
    if (whereStr) sql += `${N}WHERE ${whereStr}`;
    sql = `${sql};`;
    logType === "countUpdate" && printSqlLog(sql, "countUpdate");
    const result = await this.app.mysql.query(sql);
    return result;
  }
};
